IF exists(SELECT * FROM dbo.sysobjects WHERE name='r_jaarGemiddeldeblad' AND xtype='FN') 
BEGIN
	DROP FUNCTION r_jaarGemiddeldeblad
END
GO

CREATE   function r_jaarGemiddeldeblad
(@klas varchar(5))
 returns @resultaten table(v1 varchar(2),DW1 int, EX1 int, TOT1 int,
	v2 varchar(2),DW2 int, EX2 int, TOT2 int,
	v3 varchar(2),DW3 int, EX3 int, TOT3 int,
	v4 varchar(2),DW4 int, EX4 int, TOT4 int,
	v5 varchar(2),DW5 int, EX5 int, TOT5 int,
	v6 varchar(2),DW6 int, EX6 int, TOT6 int,
	v7 varchar(2),DW7 int, EX7 int, TOT7 int,
	v8 varchar(2),DW8 int, EX8 int, TOT8 int,
	v9 varchar(2),DW9 int, EX9 int, TOT9 int,
	v10 varchar(2),DW10 int, EX10 int, TOT10 int,
	v11 varchar(2),DW11 int, EX11 int, TOT11 int,
	v12 varchar(2),DW12 int, EX12 int, TOT12 int,
	v13 varchar(2),DW13 int, EX13 int, TOT13 int,
	v14 varchar(2),DW14 int, EX14 int, TOT14 int,
	v15 varchar(2),DW15 int, EX15 int, TOT15 int,
	v16 varchar(2),DW16 int, EX16 int, TOT16 int,
	v17 varchar(2),DW17 int, EX17 int, TOT17 int,
	v18 varchar(2),DW18 int, EX18 int, TOT18 int,
	v19 varchar(2),DW19 int, EX19 int, TOT19 int,
	v20 varchar(2),DW20 int, EX20 int, TOT20 int) 
as
begin
-- Invoegen van n record
insert into @resultaten(v1)
values(null)

-- Aanvullen van de namen van de vakken in de juiste volgorde. Het leerlingtotaal
-- krijgt de aanduiding 'AT'

declare deCursor cursor local for
select distinct vakcode,volgnummer
from leraars.dbo.alleKlasVakcodes(@klas) as v
  order by v.volgNummer

declare @vak varchar(5)
declare @volgNr int
declare @vakNr int
declare @zin varchar(200)

open deCursor
set @vakNr = 1
fetch next from deCursor into @vak,@volgNr 
while (@@fetch_status=0) and (@vakNr < 20)
begin
  if @vakNr = 1 update @resultaten set v1 = @vak
  if @vakNr = 2 update @resultaten set v2 = @vak
  if @vakNr = 3 update @resultaten set v3 = @vak
  if @vakNr = 4 update @resultaten set v4 = @vak
  if @vakNr = 5 update @resultaten set v5 = @vak
  if @vakNr = 6 update @resultaten set v6 = @vak
  if @vakNr = 7 update @resultaten set v7 = @vak
  if @vakNr = 8 update @resultaten set v8 = @vak
  if @vakNr = 9 update @resultaten set v9 = @vak
  if @vakNr = 10 update @resultaten set v10 = @vak
  if @vakNr = 11 update @resultaten set v11 = @vak
  if @vakNr = 12 update @resultaten set v12 = @vak
  if @vakNr = 13 update @resultaten set v13 = @vak
  if @vakNr = 14 update @resultaten set v14 = @vak
  if @vakNr = 15 update @resultaten set v15 = @vak
  if @vakNr = 16 update @resultaten set v16 = @vak
  if @vakNr = 17 update @resultaten set v17 = @vak
  if @vakNr = 18 update @resultaten set v18 = @vak
  if @vakNr = 19 update @resultaten set v19 = @vak
  set @vakNr = @vakNr + 1
  fetch next from deCursor into @vak,@volgNr
end
deallocate deCursor

update @resultaten set v20 = 'AT'

--Aanvullen van de jaarcijfers
declare @jaarcijfers table(
vak char(2),
DWprocent int,EXprocent int, TOTprocent int)

insert into @jaarcijfers(vak,DWprocent,EXprocent,TOTprocent)
select vak,DWprocent,EXprocent,TOTprocent 
from r_jaarVakgemiddelde(@klas)

--Aanvullen van de leerlingtotalen

insert into @jaarcijfers(vak,DWprocent,EXprocent,TOTprocent)
select vak,DWprocent,EXprocent,TOTprocent from r_jaarKlastotaal(@klas)

update @resultaten
set DW1 = case when m1.DWprocent < 0 then null else m1.DWprocent end ,
    DW2 = case when m2.DWprocent < 0 then null else m2.DWprocent end ,
    DW3 = case when m3.DWprocent < 0 then null else m3.DWprocent end,
    DW4 = case when m4.DWprocent < 0 then null else m4.DWprocent end,
    DW5 = case when m5.DWprocent < 0 then null else m5.DWprocent end,
    DW6 = case when m6.DWprocent < 0 then null else m6.DWprocent end,
    DW7 = case when m7.DWprocent < 0 then null else m7.DWprocent end,
    DW8 = case when m8.DWprocent < 0 then null else m8.DWprocent end,
    DW9 = case when m9.DWprocent < 0 then null else m9.DWprocent end,
    DW10 = case when m10.DWprocent < 0 then null else m10.DWprocent end,
    DW11 = case when m11.DWprocent < 0 then null else m11.DWprocent end,
    DW12 = case when m12.DWprocent < 0 then null else m12.DWprocent end,
    DW13 = case when m13.DWprocent < 0 then null else m13.DWprocent end,
    DW14 = case when m14.DWprocent < 0 then null else m14.DWprocent end,
    DW15 = case when m15.DWprocent < 0 then null else m15.DWprocent end,
    DW16 = case when m16.DWprocent < 0 then null else m16.DWprocent end,
    DW17 = case when m17.DWprocent < 0 then null else m17.DWprocent end,
    DW18 = case when m18.DWprocent < 0 then null else m18.DWprocent end,
    DW19 = case when m19.DWprocent < 0 then null else m19.DWprocent end,
    DW20 = case when m20.DWprocent < 0 then null else m20.DWprocent end,
    EX1 = case when m1.EXprocent < 0 then null else m1.EXprocent end ,
    EX2 = case when m2.EXprocent < 0 then null else m2.EXprocent end ,
    EX3 = case when m3.EXprocent < 0 then null else m3.EXprocent end,
    EX4 = case when m4.EXprocent < 0 then null else m4.EXprocent end,
    EX5 = case when m5.EXprocent < 0 then null else m5.EXprocent end,
    EX6 = case when m6.EXprocent < 0 then null else m6.EXprocent end,
    EX7 = case when m7.EXprocent < 0 then null else m7.EXprocent end,
    EX8 = case when m8.EXprocent < 0 then null else m8.EXprocent end,
    EX9 = case when m9.EXprocent < 0 then null else m9.EXprocent end,
    EX10 = case when m10.EXprocent < 0 then null else m10.EXprocent end,
    EX11 = case when m11.EXprocent < 0 then null else m11.EXprocent end,
    EX12 = case when m12.EXprocent < 0 then null else m12.EXprocent end,
    EX13 = case when m13.EXprocent < 0 then null else m13.EXprocent end,
    EX14 = case when m14.EXprocent < 0 then null else m14.EXprocent end,
    EX15 = case when m15.EXprocent < 0 then null else m15.EXprocent end,
    EX16 = case when m16.EXprocent < 0 then null else m16.EXprocent end,
    EX17 = case when m17.EXprocent < 0 then null else m17.EXprocent end,
    EX18 = case when m18.EXprocent < 0 then null else m18.EXprocent end,
    EX19 = case when m19.EXprocent < 0 then null else m19.EXprocent end,
    EX20 = case when m20.EXprocent < 0 then null else m20.EXprocent end,
    TOT1 = case when m1.TOTprocent < 0 then null else m1.TOTprocent end ,
    TOT2 = case when m2.TOTprocent < 0 then null else m2.TOTprocent end ,
    TOT3 = case when m3.TOTprocent < 0 then null else m3.TOTprocent end,
    TOT4 = case when m4.TOTprocent < 0 then null else m4.TOTprocent end,
    TOT5 = case when m5.TOTprocent < 0 then null else m5.TOTprocent end,
    TOT6 = case when m6.TOTprocent < 0 then null else m6.TOTprocent end,
    TOT7 = case when m7.TOTprocent < 0 then null else m7.TOTprocent end,
    TOT8 = case when m8.TOTprocent < 0 then null else m8.TOTprocent end,
    TOT9 = case when m9.TOTprocent < 0 then null else m9.TOTprocent end,
    TOT10 = case when m10.TOTprocent < 0 then null else m10.TOTprocent end,
    TOT11 = case when m11.TOTprocent < 0 then null else m11.TOTprocent end,
    TOT12 = case when m12.TOTprocent < 0 then null else m12.TOTprocent end,
    TOT13 = case when m13.TOTprocent < 0 then null else m13.TOTprocent end,
    TOT14 = case when m14.TOTprocent < 0 then null else m14.TOTprocent end,
    TOT15 = case when m15.TOTprocent < 0 then null else m15.TOTprocent end,
    TOT16 = case when m16.TOTprocent < 0 then null else m16.TOTprocent end,
    TOT17 = case when m17.TOTprocent < 0 then null else m17.TOTprocent end,
    TOT18 = case when m18.TOTprocent < 0 then null else m18.TOTprocent end,
    TOT19 = case when m19.TOTprocent < 0 then null else m19.TOTprocent end,
    TOT20 = case when m20.TOTprocent < 0 then null else m20.TOTprocent end
from @resultaten as r
  left join (select DWprocent,EXprocent,TOTprocent,vak from @jaarcijfers) as m1
	on m1.vak = r.v1
  left join (select DWprocent,EXprocent,TOTprocent,vak from @jaarcijfers) as m2
	on  m2.vak = r.v2
  left join (select DWprocent,EXprocent,TOTprocent,vak from @jaarcijfers) as m3
	on m3.vak = r.v3
  left join (select DWprocent,EXprocent,TOTprocent,vak from @jaarcijfers) as m4
	on m4.vak = r.v4
  left join (select DWprocent,EXprocent,TOTprocent,vak from @jaarcijfers) as m5
	on m5.vak = r.v5
  left join (select DWprocent,EXprocent,TOTprocent,vak from @jaarcijfers) as m6
	on m6.vak = r.v6
  left join (select DWprocent,EXprocent,TOTprocent,vak from @jaarcijfers) as m7
	on m7.vak = r.v7
  left join (select DWprocent,EXprocent,TOTprocent,vak from @jaarcijfers) as m8
	on m8.vak = r.v8
  left join (select DWprocent,EXprocent,TOTprocent,vak from @jaarcijfers) as m9
	on m9.vak = r.v9
  left join (select DWprocent,EXprocent,TOTprocent,vak from @jaarcijfers) as m10
	on m10.vak = r.v10
  left join (select DWprocent,EXprocent,TOTprocent,vak from @jaarcijfers) as m11
	on m11.vak = r.v11
  left join (select DWprocent,EXprocent,TOTprocent,vak from @jaarcijfers) as m12
	on m12.vak = r.v12
  left join (select DWprocent,EXprocent,TOTprocent,vak from @jaarcijfers) as m13
	on m13.vak = r.v13
  left join (select DWprocent,EXprocent,TOTprocent,vak from @jaarcijfers) as m14
	on m14.vak = r.v14
  left join (select DWprocent,EXprocent,TOTprocent,vak from @jaarcijfers) as m15
	on m15.vak = r.v15
  left join (select DWprocent,EXprocent,TOTprocent,vak from @jaarcijfers) as m16
	on m16.vak = r.v16
  left join (select DWprocent,EXprocent,TOTprocent,vak from @jaarcijfers) as m17
	on m17.vak = r.v17
  left join (select DWprocent,EXprocent,TOTprocent,vak from @jaarcijfers) as m18
	on m18.vak = r.v18
  left join (select DWprocent,EXprocent,TOTprocent,vak from @jaarcijfers) as m19
	on m19.vak = r.v19
  left join (select DWprocent,EXprocent,TOTprocent,vak from @jaarcijfers) as m20
	on m20.vak = r.v20
return
end
----------

--\end{verbatim}